import { Service } from 'egg';
import * as blue from 'bluebird';
import * as _ from 'lodash';
import * as Excel from 'exceljs';
import { ReadPreference } from 'mongodb';

const DB_ACCOUNT_PRODUCT = 'platform_account_product';
// const DB_ACCOUNT = 'platform_account';
const DB_SMS = '_sms';

let reportCon = {};
let reportConTemp = {};

let reportHour = {};
let reportMonth = {};

let time1;
let lock_export = false;

const sheetColumn = [
  { key: '_id', header: 'id' } as any,
  { key: 'account', header: 'account' } as any,
  { key: 'submitTime', header: 'submitTime' } as any,
  { key: 'num', header: 'num' } as any,
  { key: 'content', header: 'content' } as any,
  { key: 'sendType', header: 'sendType' } as any,
  { key: 'template', header: 'template' } as any,
  { key: 'templateid', header: 'templateid' } as any,
  { key: 'vars', header: 'vars' } as any,
  { key: 'smsChannel', header: 'smsChannel' } as any,
  { key: 'numCount', header: 'numCount' } as any,
  { key: 'splitCount', header: 'splitCount' } as any,
  { key: 'triggerId', header: 'triggerId' } as any,
  { key: 'm7Status', header: 'm7Status' } as any,
  { key: 'sign', header: 'sign' } as any,
  { key: 'createUser', header: 'createUser' } as any,
  { key: 'signDisplay', header: 'signDisplay' } as any,
  { key: 'product', header: 'product' } as any,
  { key: 'type', header: 'type' } as any,
  { key: 'isBatch', header: 'isBatch' } as any,
  { key: 'templateType', header: 'templateType' } as any,
  { key: 'by_errordescription', header: 'by_errordescription' } as any,
  { key: 'gd_msgid', header: 'gd_msgid' } as any,
  { key: 'operation', header: 'operation' } as any,
];

export default class SjSmsTopService extends Service {

  // public async getAccounts(): Promise<{dataDB: string, account:string}[]> {
  //   const accounts = await this.app.commonDB.db('bill').collection(DB_ACCOUNT_PRODUCT)
  //     .find({product: 'cc', createTime: {$lt: '2020-09-01 00:00:00'}}, {fields: {_id: 1, dataDB:1, account:1}}).toArray();
  //   this.ctx.logger.info(accounts.length);
  //   return accounts as any;
  // }

  /**
   * 请研发抽取6月份发送量最大的前十家公司，各抽取其前1万条短信明细
   */
  public async sjsmsTop10() {
    const body = this.ctx.request.query;
    // const {startTime, endTime, top} = body; 
    const startTime = body.startTime || '2020-06-01 00:00:00';
    const endTime = body.endTime || '2020-07-01 00:00:00';
    const top = parseInt(body.top) || 10;
    // const concurrency: number = parseInt(body.concurrency) || 10;

    let ans: {accountId: string, count: number, dataDB: string}[] = [];

    const query = {
      submitTime: {
        $gte: startTime,
        $lte: endTime,
      },
      m7Status: {$in: ["send","deliver","undeliver"] }, // 发送状态 取值(deliver, forbiddenWord, import, ready, send, sendFail, undeliver)
    };

    const accounts = await this.getAccounts();
    const all = accounts.length;

    let current = 0;
    await blue.map(accounts, async(accountDoc) => {
      const accountId = accountDoc.account;
      const datadb = accountDoc.dataDB;

      const oneAll = await this.app.commonDB.db(datadb).collection(`${accountId}${DB_SMS}`)
        .find(query)
        .setReadPreference(new ReadPreference('secondary',[{'offline': 'true'}]))
        .count();
      ans[accountId] = oneAll;
      if (oneAll > 0) {
        ans.push({
          accountId,
          count: oneAll,
          dataDB: datadb,
        });
        this.app.logger.info('current_count_top=', all, ++current);
      }
      

    }, {concurrency: 10});
    this.app.logger.info('计算万完成', ans.length);
    ans.sort((a, b) => {
      return b.count - a.count;
    });

    ans = ans.slice(0, top);
    this.app.logger.info('开始导出', ans);

    const filename = `./logs/lismstop10-${startTime}-${Date.now()}.xlsx`;
    const options = {
      filename,
      useStyles: true,
      useSharedStrings: true
    };
    const workbook = new Excel.stream.xlsx.WorkbookWriter(options);
    // const workbook = new Excel.Workbook(options);

    // sheet1
    let sheet = workbook.addWorksheet(`客户top10统计${startTime}-${endTime}`);
    sheet.columns = [
      {key: 'accountId', header: '账户'} as any,
      {key: 'count', header: '发送量'} as any,
    ];
    ans.forEach((e) => {
      sheet.addRow(e).commit();
    });
    sheet.commit();

    await blue.map(ans, async (one) => {
      this.app.logger.info(`${one.accountId}导出 ${one.count}`)
      if (one.count <= 0) {
        return;
      }
      const sheet2 = workbook.addWorksheet(`${one.accountId}详情`);
      sheet2.columns = sheetColumn;

      const cursor = await this.app.commonDB.db(one.dataDB).collection(`${one.accountId}${DB_SMS}`).find(query)
        .setReadPreference(new ReadPreference('secondary',[{'offline': 'true'}])).batchSize(10000).limit(10000);

      while (await cursor.hasNext()) {
        const doc = await cursor.next();
        sheet2.addRow(doc).commit();
      }

      sheet2.commit();
    });

    await workbook.commit();

    this.app.logger.info('导出完成');
    this.ctx.setSuccess();
    this.ctx.setField('list', ans);
    this.ctx.setField('time', query.submitTime);
    this.ctx.sendResult();
    return;
  }


  /**
   * 2020年09月24日20:43:03 审计需求
   */
  public async sjsmsDetail() {
    time1 = new Date().getTime();
    reportCon = {};
    reportConTemp = {};
    reportHour = {};
    reportMonth = {};
    lock_export = false;

    const body: {
      startTime: string;
      endTime: string;
      count: string;
      account: string;
    } = this.ctx.request.query as any;
    const {startTime, endTime} = body;
    const count = parseInt(body.count);

    const query = {
      submitTime: {
        $gte: startTime + ' 00:00:00',
        $lte: endTime + ' 00:00:00',
      },
      m7Status: {$in: ["send","deliver","undeliver"] }, // 发送状态 取值(deliver, forbiddenWord, import, ready, send, sendFail, undeliver)
      /**
       * ready 初始状态 单发
       * import 初始状态 群发
       * send 请求之后有一些没有返回状态
       * deliver 发送成功
       * undeliver 投递失败
       * sendFail 发送失败
       * forbiddenWord 敏感词禁用
       */
    };
    this.app.logger.info('query: ', query);

    let accounts = await this.getAccounts(body.account);

    const all = accounts.length;

    let current = 0;
    try {
      await blue.map(accounts, async(accountDoc) => {
        const accountId = accountDoc.account;
        const datadb = accountDoc.dataDB;
  
        reportConTemp = {};
  
        const oneAll = await this.app.commonDB.db(datadb).collection(`${accountId}${DB_SMS}`).count(query);
        if (oneAll === 0) {
          current++;
          this.printRate(all, current);
          return;
        }
        let oneCurrent = 0;
  
        const cursor = await this.app.commonDB.db(datadb).collection(`${accountId}${DB_SMS}`).find(query, {fields: {
          submitTime: 1,
          num: 1,
        }});
  
        let doc: { submitTime: string, num: string, };
        while (await cursor.hasNext()) {
          doc = await cursor.next();
          // this.ctx.logger.info('doc', doc);
          this.processOneSms(doc);
          oneCurrent++;
          this.printOneRate(oneAll, oneCurrent);
        }
        const ansNumber = this.processOneAccountEnd(count);
        if (ansNumber) {
          this.ctx.logger.error('exporting' + ansNumber + accountId);
          await this.exportSmsByAccountAndNumber(datadb, accountId, {...query, num: ansNumber}, startTime, count);
          throw new Error("exit");
        }
        current++;
        this.printRate(all, current);
  
      }, {concurrency: 1});
    } catch (error) {
      this.ctx.logger.info('export over', error.message);
      return this.ctx.sendSuccess();
    }
    this.ctx.logger.info('export over, notfound');
    return this.ctx.sendError();
  }


  public async exportSmsByAccountAndNumber(datadb: string, accountId: string, query: any, startTime: string, count: number ) {
    if (!lock_export) {
      lock_export = true;
    } else {
      this.logger.info('已经导出了');
      return;
    }
    const filename = `./logs/detailsms-${startTime}-${count}-${Date.now()}.xlsx`;
    const options = {
      filename,
      useStyles: true,
      useSharedStrings: true
    };
    const workbook = new Excel.stream.xlsx.WorkbookWriter(options);
    // const workbook = new Excel.Workbook(options);

    // sheet1
    const sheet = workbook.addWorksheet(`明细-${startTime}-${count}`);
    sheet.columns = [
      { key: '_id', header: 'id' } as any,
      { key: 'account', header: 'account' } as any,
      { key: 'submitTime', header: 'submitTime' } as any,
      { key: 'num', header: 'num' } as any,
      { key: 'content', header: 'content' } as any,
      { key: 'sendType', header: 'sendType' } as any,
      { key: 'template', header: 'template' } as any,
      { key: 'templateid', header: 'templateid' } as any,
      { key: 'vars', header: 'vars' } as any,
      { key: 'smsChannel', header: 'smsChannel' } as any,
      { key: 'numCount', header: 'numCount' } as any,
      { key: 'splitCount', header: 'splitCount' } as any,
      { key: 'triggerId', header: 'triggerId' } as any,
      { key: 'm7Status', header: 'm7Status' } as any,
      { key: 'sign', header: 'sign' } as any,
      { key: 'createUser', header: 'createUser' } as any,
      { key: 'signDisplay', header: 'signDisplay' } as any,
      { key: 'product', header: 'product' } as any,
      { key: 'type', header: 'type' } as any,
      { key: 'isBatch', header: 'isBatch' } as any,
      { key: 'templateType', header: 'templateType' } as any,
      { key: 'by_errordescription', header: 'by_errordescription' } as any,
      { key: 'gd_msgid', header: 'gd_msgid' } as any,
      { key: 'operation', header: 'operation' } as any,
    ];
    const cursor = await this.app.commonDB.db(datadb).collection(`${accountId}${DB_SMS}`).find(query).limit(10000);
    while (await cursor.hasNext()) {
      const doc = await cursor.next();
      sheet.addRow(doc).commit();
    }
    sheet.commit();
    
    console.time('执行commitdetail');
    await workbook.commit();
    console.timeEnd('执行commitdetail');
    const time2 = new Date().getTime();
    this.ctx.logger.info(`导出耗时：${time2 - time1} ms`);
  }

  public async printRate(all: number, current: number) {
    if (current % 100 === 0) {
      this.app.logger.info('=====================================================================');
      this.app.logger.info('======account==rate======' + `${all} ${current} ${Math.round(current/all*100)}%`);
    }
  }

  public async printOneRate(all: number, current: number) {
    if (current % 1000 === 0) {
      this.app.logger.info('======one====rate========' + `${all} ${current} ${Math.round(current/all*100)}%`);
    }
  }

  public async processAllEnd() {
    const filename = `./logs/detailsj-sms-${Date.now()}.xlsx`;
    const options = {
      filename,
      useStyles: true,
      useSharedStrings: true
    };
    const workbook = new Excel.stream.xlsx.WorkbookWriter(options);
    // const workbook = new Excel.Workbook(options);

    // sheet1
    const sheet = workbook.addWorksheet('单号集中度');
    sheet.columns = [
      { key: 'month', header: '月份' } as any,
      { key: 'count', header: '单手机号接收短信数量' } as any,
      { key: 'phone_count', header: '手机号数量' } as any,
    ];
    let months = Object.keys(reportCon);
    months = months.sort((a, b) => {
      return a > b ? 1: -1
    });
    months.forEach((month) => {
      const one = reportCon[month];
      let list = Object.keys(one);
      list = list.sort((a, b) => {
        return parseInt(a) < parseInt(b) ? 1: -1
      });
      list.forEach((count) => {
        const obj = {
          month,
          count,
          phone_count: one[count],
        };
        sheet.addRow(obj).commit();;
      })
    });
    sheet.commit();

    // sheet2
    const sheet2 = workbook.addWorksheet('短信发送时间分布');
    sheet2.columns = [
      { key: 'time', header: '时间段' } as any,
      { key: 'count', header: '短信发送量' } as any,
    ];
    let houres = Object.keys(reportHour);
    houres = houres.sort((a, b) => {
      return a > b ? 1: -1
    });
    houres.forEach((hour) => {
      sheet2.addRow({
        time: hour,
        count: reportHour[hour],
      }).commit();
    });
    sheet2.commit();

    // sheet3
    const sheet3 = workbook.addWorksheet('短信发送量趋势分析');
    sheet3.columns = [
      { key: 'time', header: '月份' } as any,
      { key: 'count', header: '短信发送量' } as any,
    ];
    let monthList = Object.keys(reportMonth);
    monthList = monthList.sort((a, b) => {
      return a > b ? 1: -1
    });
    monthList.forEach((month) => {
      sheet3.addRow({
        time: month,
        count: reportMonth[month],
      }).commit();
    });
    this.app.logger.info('生成表格成功')
    sheet3.commit();

    console.time('执行commit');
    await workbook.commit();
    console.timeEnd('执行commit');
    const time2 = new Date().getTime();
    this.ctx.logger.info(`导出耗时：${time2 - time1} ms`);
  }

  public processOneAccountEnd(toSjCount: number) {
    // 处理report1
    let ansnumber;
    Object.keys(reportConTemp).forEach((month) => {
      const dis = reportConTemp[month];
      if (!reportCon[month]) {
        reportCon[month] = {};
      }
      const one = reportCon[month];

      Object.keys(dis).forEach(num => {
        const count = dis[num];
        this.app.logger.info(`num: ${num}, count: ${count}`);
        if (count === toSjCount) {
          ansnumber = num; 
        }
        if (!one[count]) {
          one[count] = 0;
        }
        one[count]++;
      });
    });
    reportConTemp = {};
    return ansnumber;
    // other
  }

  public processOneSms(sms: { submitTime: string, num: string }) {
    const month = this.getMonthFromTime(sms.submitTime);
    // const hour = this.getHourFromTime(sms.submitTime);
    this.reportConcentrate(month, sms.num);
    // this.reportHour(hour);
    // this.reportMonth(month);
  }

  // 1.DA1单手机号接受短信数量集中度分析
  public reportConcentrate(month: string, num: string) {
    if (!reportConTemp[month]) {
      reportConTemp[month] = {};
    }
    if (!reportConTemp[month][num]) {
      reportConTemp[month][num] = 0;
    }
    reportConTemp[month][num]++;
  }

  //2.DA2 短信发送时间分布分析
  public reportHour(hour: string) {
    if (!reportHour[hour]) {
      reportHour[hour] = 0;
    }
    reportHour[hour]++;
  }

  // 3.DA4 短信发送量趋势分析
  public reportMonth(month: string) {
    if (!reportMonth[month]) {
      reportMonth[month] = 0;
    }
    reportMonth[month]++;
  }

  // "2020-08-18 16:02:26" => "202008"
  public getMonthFromTime(time: string): string {
    return time.substr(0, 4) + time.substr(5, 2);
  }

  // "2020-08-18 16:02:26" => "16"
  public getHourFromTime(time: string): string {
    return time.substr(11,2);
  }

  public async findSms(account: string, datadb: string) {
    return this.app.commonDB.db(datadb).collection(`${account}_${DB_SMS}`).find({});
  }

  public async getAccounts(account?: string): Promise<{dataDB: string, account:string}[]> {
    const query: any = {product: 'cc', createTime: {$lt: '2020-09-01 00:00:00'}};
    if (account) {
      query.account = account;
    }
    const accounts = await this.app.commonDB.db('bill').collection(DB_ACCOUNT_PRODUCT)
      .find(query, {fields: {_id: 1, dataDB:1, account:1}}).toArray();
    this.ctx.logger.info(accounts.length);
    return accounts as any;
  }

  // public async getDataDB(account) {
  //   let db = dbCache[account];
  //   if (!db) {
  //     const doc = await this.app.commonDB.db('bill').collection(DB_ACCOUNT_PRODUCT)
  //       .findOne({_id: account + '_cc'}, {fields: {dataDB:1}});
  //     if (!doc) {
  //       return null;
  //     }
  //     dbCache[account] = doc.dataDB;
  //     db = doc.dataDB;
  //   }
  //   return db;
  // }

}
